package com.bringway.p2p.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.BaseJdbcDao;
import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.framework.util.RequestUtils;
import com.bringway.p2p.dao.CjDao;

import net.sf.json.JSONObject;
@Repository
public class CjDaoImpl extends BaseJdbcDao implements CjDao {
	private Logger logger=Logger.getLogger(CjDaoImpl.class);

	@Override
	public List<Map<String, Object>> queryCjListAll(JSONObject params) {
		int start=params.optInt("start");
		int limit=params.optInt("limit");
		String gyh=params.optString("gyh");
		String xm=params.optString("xm");
		String iszj=params.optString("iszj");
		boolean isdown=params.optBoolean("isdown");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select ID, GYH, REAL_NAME, RYGW, XB, PIC_PATH,date_format(CG_DATE,'%Y-%m-%d %H:%i:%s') CG_DATE,IS_ZJ,ZJ_LEVEL,date_format(ZJ_DATE,'%Y-%m-%d %H:%i:%s') ZJ_DATE from d_ccb_fm.t_fm_znnh_userinfo where 1=1");
		if (StringUtils.isNotEmpty(gyh)) {
			sql.append(" and GYH like :gyh");
			paramMap.put("gyh", "%"+gyh+"%");
		}
		if (StringUtils.isNotEmpty(xm)) {
			sql.append(" and REAL_NAME like :xm");
			paramMap.put("xm", "%"+xm+"%");
		}
		if (StringUtils.isNotEmpty(iszj)) {
			if(StringUtils.equals(iszj, "0")){
				sql.append(" and IS_ZJ is null");
			}else {
				sql.append(" and IS_ZJ =:iszj");
				paramMap.put("iszj", iszj);
			}
		}
		if (isdown) {
			sql.append(" order by ZJ_LEVEL ASC,ZJ_DATE ASC");
		}else {
			sql.append(" order by ID ASC ");
		}
		if (limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryCjListAll sql is "+sql.toString());
		logger.info("queryCjListAll paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@SuppressWarnings("deprecation")
	@Override
	public int getCjListCount(JSONObject params) {
		String gyh=params.optString("gyh");
		String xm=params.optString("xm");
		String iszj=params.optString("iszj");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select count(*) from d_ccb_fm.t_fm_znnh_userinfo where 1=1");
		if (StringUtils.isNotEmpty(gyh)) {
			sql.append(" and GYH like :gyh");
			paramMap.put("gyh", "%"+gyh+"%");
		}
		if (StringUtils.isNotEmpty(xm)) {
			sql.append(" and REAL_NAME like :xm");
			paramMap.put("xm", "%"+xm+"%");
		}
		if (StringUtils.isNotEmpty(iszj)) {
			if(StringUtils.equals(iszj, "0")){
				sql.append(" and IS_ZJ is null");
			}else {
				sql.append(" and IS_ZJ =:iszj");
				paramMap.put("iszj", iszj);
			}
		}
		logger.info("getCjListCount sql is "+sql.toString());
		logger.info("getCjListCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public int insertCj(JSONObject params) {
		String gyh=params.optString("gyh");
		String xm=params.optString("xm");
		String rygw=params.optString("rygw");
		String xb=params.optString("xb");
		String contentType=params.optString("contentType");
		String picPath=params.optString("picPath");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("insert into d_ccb_fm.t_fm_znnh_userinfo(GYH,REAL_NAME,RYGW,XB,PIC_PATH,CONTENT_TYPE,CG_DATE)");
		sql.append(" values(:gyh,:xm,:rygw,:xb,:picPath,:contentType,now())");
		paramMap.put("gyh", gyh);
		paramMap.put("xm", xm);
		paramMap.put("rygw", rygw);
		paramMap.put("xb", xb);
		paramMap.put("contentType", contentType);
		paramMap.put("picPath", picPath);
		logger.info("insertCj sql is "+sql.toString());
		logger.info("insertCj paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public int isBe(String gyh) {
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select count(*) from d_ccb_fm.t_fm_znnh_userinfo where gyh=:gyh");
		paramMap.put("gyh", gyh);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public int updateCj(JSONObject params) {
		String gyh=params.optString("gyh");
		String xm=params.optString("xm");
		String rygw=params.optString("rygw");
		String xb=params.optString("xb");
		String contentType=params.optString("contentType");
		String picPath=params.optString("picPath");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("update d_ccb_fm.t_fm_znnh_userinfo set CG_DATE=now()");
		if (StringUtils.isNotEmpty(xm)) {
			sql.append(",REAL_NAME=:xm");
			paramMap.put("xm", xm);
		}
		if (StringUtils.isNotEmpty(rygw)) {
			sql.append(",RYGW=:rygw");
			paramMap.put("rygw", rygw);
		}
		if (StringUtils.isNotEmpty(xb)) {
			sql.append(",XB=:xb");
			paramMap.put("xb", xb);
		}
		if (StringUtils.isNotEmpty(contentType)) {
			sql.append(",CONTENT_TYPE=:contentType");
			paramMap.put("contentType", contentType);
		}
		if (StringUtils.isNotEmpty(picPath)) {
			sql.append(",PIC_PATH=:picPath");
			paramMap.put("picPath", picPath);
		}
		sql.append(" where GYH=:gyh");
		paramMap.put("gyh", gyh);
		logger.info("updateCj sql is "+sql.toString());
		logger.info("updateCj paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public int deleteCj(JSONObject params) {
		String id=params.optString("id");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("delete from d_ccb_fm.t_fm_znnh_userinfo where ID=:id");
		paramMap.put("id", id);
		logger.info("deleteCj sql is "+sql.toString());
		logger.info("deleteCj paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public Map getZnnhUserinfoByGyh(String gyh) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		sql.append("select ID, GYH, REAL_NAME, RYGW, XB, PIC_PATH,date_format(CG_DATE,'%Y-%m-%d %H:%i:%s') CG_DATE,IS_ZJ,ZJ_LEVEL,ZJ_DATE from d_ccb_fm.t_fm_znnh_userinfo where 1=1");
		if (StringUtils.isNotEmpty(gyh)) {
			sql.append(" and GYH = :gyh");
			paramMap.put("gyh", gyh);
		}
		logger.info("getZnnhUserinfoByGyh sql is "+sql.toString());
		logger.info("getZnnhUserinfoByGyh paramMap is "+paramMap);
		if(this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap).size() > 0){
			return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
		}
		return null;
	}

	@Override
	public List getZjList(String num, String level) {
		
		
		
		return null;
	}

	@Override
	public int getDzjListCount(JSONObject params) {
		Map<String, Object> paramMap = new HashMap<>();
		String sql = " SELECT COUNT(*) FROM t_fm_znnh_userinfo WHERE IS_ZJ is null or IS_ZJ <> '1' ";
		logger.info("getDzjListCount sql : " + sql);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql, paramMap, Integer.class);
	}

	@Override
	public Map<String,Object> getzjGyh(int zjRandom) {
		Map<String, Object> paramMap = new HashMap<>();
		String sql = " SELECT * FROM d_ccb_fm.t_fm_znnh_userinfo WHERE IS_ZJ is null or IS_ZJ <> '1'  order by ID asc limit "+zjRandom+",1 ";
		logger.info("getzjGyh sql : " + sql);
		if(this.getNamedParameterJdbcTemplate().queryForList(sql, paramMap).size() > 0){
			return this.getNamedParameterJdbcTemplate().queryForMap(sql.toString(), paramMap);
		}else{
			logger.info(" 无法获得中奖人员信息... ");
			return null;
		}
	}

	@Override
	public void updateZjInfo(String zjGyh,String level) {
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<>();
		sql.append(" update t_fm_znnh_userinfo set IS_ZJ = '1',ZJ_LEVEL=:LEVEL,ZJ_DATE=now() where GYH=:GYH ");
		paramMap.put("GYH", zjGyh);
		paramMap.put("LEVEL", level);
		logger.info("updateZjInfo sql is : " + sql.toString());
		logger.info("updateZjInfo paramMap is : " + paramMap);
		this.getNamedParameterJdbcTemplate().update(sql.toString(), paramMap);
	}

	@Override
	public int getYzjCount(String level) {
		Map<String, Object> paramMap = new HashMap<>();
		String sql = "  SELECT COUNT(*) CT FROM t_fm_znnh_userinfo where IS_ZJ = '1' AND ZJ_LEVEL = :ZJ_LEVEL ";
		paramMap.put("ZJ_LEVEL", level);
		logger.info("getDzjListCount sql : " + sql);
		return this.getNamedParameterJdbcTemplate().queryForObject(sql, paramMap, Integer.class);
	}

	@Override
	public boolean checkUserSafe(String password) {
		Map<String, Object> paramMap = new HashMap<>();
		String sql = " select count(*) ct from t_fm_sys_user where gyh = '01012' and user_password = md5('"+password+"') ";
		logger.info("checkUserSafe sql is : " + sql);
		int ct = this.getNamedParameterJdbcTemplate().queryForObject(sql, paramMap, Integer.class);
		if(ct > 0 ){
			return true;
		}
		return false;
	}

	@Override
	public boolean isTscl() {
		StringBuffer sql = new StringBuffer();
		sql.append(" select CONFIG_VALUE from t_fm_sys_config where CONFIG_KEY = 'ZNNH_CJ' ");
		logger.info(" isTscl sql is : " + sql.toString());
		String value = this.getNamedParameterJdbcTemplate().query(sql.toString(), new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				if(rs.next()){
					return rs.getString("CONFIG_VALUE");
				}
				return null;
			}
		});
		if(StringUtils.isNotEmpty(value) && "1".equals(value)){
			return true;
		}
		return false;
	}

	@Override
	public List<Map<String, Object>> listZnqCj(JSONObject params) {
		int start=params.optInt("start");
		int limit=params.optInt("limit");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String startDateLq = params.optString("startDateLq");
		String endDateLq = params.optString("endDateLq");
		String username = params.optString("username");
		String xm = params.optString("xm");
		String sjhm = params.optString("sjhm");
		String cjType = params.optString("cjType");
		String lqState = params.optString("lqState");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select t1.ID,t1.USERNAME,t1.SJHM,t1.REAL_NAME,t1.CJ_TYPE,t1.JX,date_format(t1.CJSJ,'%Y-%m-%d') CJSJ");
		sql.append(",t1.LQ_STATE,t1.LQ_FGSMD,date_format(t1.USER_CZSJ,'%Y-%m-%d') USER_CZSJ,t1.FF_GYH");
		sql.append(",date_format(t1.FFSJ,'%Y-%m-%d') FFSJ,t1.FF_COMMENT,t1.LY_TYPE,t2.TJR,t2.TJJB,t1.USER_COMMENT from d_hd_p2p.t_zhfw_cjjg t1");
		sql.append(" left join d_hd_p2p.t_zhfw_tjr_tjjb t2 on t2.USER_NAME=t1.USERNAME where 1=1 and t1.JX in ('1','2','3','4')");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t1.CJSJ,'%Y-%m-%d') >=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t1.CJSJ,'%Y-%m-%d') <=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(startDateLq)) {
			sql.append(" and date_format(t1.USER_CZSJ,'%Y-%m-%d') >=:startDateLq");
			paramMap.put("startDateLq", startDateLq);
		}
		if (StringUtils.isNotEmpty(endDateLq)) {
			sql.append(" and date_format(t1.USER_CZSJ,'%Y-%m-%d') <=:endDateLq");
			paramMap.put("endDateLq", endDateLq);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and t1.USERNAME =:username");
			paramMap.put("username", username);
		}
		if (StringUtils.isNotEmpty(xm)) {
			sql.append(" and t1.REAL_NAME =:xm");
			paramMap.put("xm", xm);
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and t1.SJHM =:sjhm");
			paramMap.put("sjhm", sjhm);
		}
		if (StringUtils.isNotEmpty(cjType)) {
			sql.append(" and t1.CJ_TYPE =:cjType");
			paramMap.put("cjType", cjType);
		}
		if (StringUtils.isNotEmpty(lqState)) {
			sql.append(" and t1.LQ_STATE =:lqState");
			paramMap.put("lqState", lqState);
		}
		sql.append(" order by CJSJ desc");
		if (limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("listZnqCj sql is "+sql.toString());
		logger.info("listZnqCj paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);
	}

	@Override
	public int getZnqCjCount(JSONObject params) {
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		String startDateLq = params.optString("startDateLq");
		String endDateLq = params.optString("endDateLq");
		String username = params.optString("username");
		String xm = params.optString("xm");
		String sjhm = params.optString("sjhm");
		String cjType = params.optString("cjType");
		String lqState = params.optString("lqState");
		StringBuffer sql=new StringBuffer();
		Map<String, Object> paramMap=new HashMap<>();
		sql.append("select count(*) from d_hd_p2p.t_zhfw_cjjg t1");
		sql.append(" left join d_hd_p2p.t_zhfw_tjr_tjjb t2 on t2.USER_NAME=t1.USERNAME where 1=1 and t1.JX in ('1','2','3','4')");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" and date_format(t1.CJSJ,'%Y-%m-%d') >=:startDate");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" and date_format(t1.CJSJ,'%Y-%m-%d') <=:endDate");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(startDateLq)) {
			sql.append(" and date_format(t1.USER_CZSJ,'%Y-%m-%d') >=:startDateLq");
			paramMap.put("startDateLq", startDateLq);
		}
		if (StringUtils.isNotEmpty(endDateLq)) {
			sql.append(" and date_format(t1.USER_CZSJ,'%Y-%m-%d') <=:endDateLq");
			paramMap.put("endDateLq", endDateLq);
		}
		if (StringUtils.isNotEmpty(username)) {
			sql.append(" and t1.USERNAME =:username");
			paramMap.put("username", username);
		}
		if (StringUtils.isNotEmpty(xm)) {
			sql.append(" and t1.REAL_NAME =:xm");
			paramMap.put("xm", xm);
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" and t1.SJHM =:sjhm");
			paramMap.put("sjhm", sjhm);
		}
		if (StringUtils.isNotEmpty(cjType)) {
			sql.append(" and t1.CJ_TYPE =:cjType");
			paramMap.put("cjType", cjType);
		}
		if (StringUtils.isNotEmpty(lqState)) {
			sql.append(" and t1.LQ_STATE =:lqState");
			paramMap.put("lqState", lqState);
		}
		logger.info("getZnqCjCount sql is "+sql.toString());
		logger.info("getZnqCjCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

	@Override
	public boolean updateZnqCj(JSONObject params) {
		String id=params.optString("id");
		String ffGyh=params.optString("ffGyh");
		String ffCom=params.optString("ffCom");
		String sql="update d_hd_p2p.t_zhfw_cjjg set LQ_STATE=:lqState,FF_GYH=:ffGyh,FFSJ=now(),FF_COMMENT=:ffCom where ID=:id";
		Map<String, Object> paramMap=new HashMap<>();
		paramMap.put("id", id);
		paramMap.put("ffGyh", ffGyh);
		paramMap.put("ffCom", ffCom);
		paramMap.put("lqState", "02");
		logger.info("updateZnqCj sql is "+sql);
		logger.info("updateZnqCj paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().update(sql, paramMap)>0;
	}
	
	

}
